-- This demo is shows the difference between BIN and BIN2 collations.
-- © Erland Sommarskog 2024.
USE Latin1_General_CI_AS
go
-- Create table with some two-character sequences.
CREATE TABLE binarytest (BIN2_col    nchar(3) COLLATE Croatian_BIN2 NOT NULL,
                         BIN_col     nchar(3) COLLATE Croatian_BIN  NOT NULL, 
                         binarycol   binary(6) NOT NULL)
go
INSERT binarytest(BIN2_col, BIN_col, binarycol)
   VALUES(N'ABB', N'ABB', convert(binary(6), N'ABB')), 
         (N'AĆĆ', N'AĆĆ', convert(binary(6), N'AĆĆ')), 
         (N'ĐĆĆ', N'ĐĆĆ', convert(binary(6), N'ĐĆĆ'))
go
-- Look at the code-point values, both in decimal and in hex.
SELECT unicode(N'A') AS A, 
       unicode(N'B') AS B, 
       unicode(N'Ć') AS Ć,
       unicode(N'Đ') AS Đ 

SELECT convert(binary(2), unicode(N'A')) AS A, 
       convert(binary(2), unicode(N'B')) AS B, 
       convert(binary(2), unicode(N'Ć')) AS Ć,
       convert(binary(2), unicode(N'Đ')) AS Đ 
go
-- First sort by casting to binary. Bytes are swapped, because Wintel is a
-- little-endian architecture.
SELECT *
FROM   binarytest
ORDER  BY binarycol
go
-- With a BIN2 collation, you get ordering by code points. That is, byte pairs 
-- are swapped.
SELECT *
FROM   binarytest
ORDER  BY BIN2_col
go
-- In a BIN collation, only first byte pair is swapped.
SELECT *
FROM   binarytest
ORDER  BY BIN_col
